﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;

namespace SeriesUpdatesCommon
{
    /// <summary>
    /// CLASSE ACCESSIBLE VIA SINGLETON - PERMET LACCES A LA BASE DE DONNEES EN LECTURE / ECRITURE
    /// AJOUT DE SERIES
    /// MODIFICATION DE SERIE
    /// ...
    /// </summary>
    public class DB
    {
        public static DB instance;
        private SQLiteConnection _connexion;
        private bool _opened;

        // Constructeur privé pour ne pas pouvoir instancier cette classe...
        private DB() { } 

        public static DB GetDB()
        {
            if (instance == null)
            {
                instance = new DB();
            }

            return instance;
        }

        private void OpenConnexion()
        {
            if (!this._opened)
            {
                this._connexion.Open();
                this._opened = true;
            }
        }

        private void CloseConnexion()
        {
            if (this._opened)
            {
                this._connexion.Close();
                this._opened = false;
            }
        }

        public void CreateBase()
        {
            using (this._connexion = new SQLiteConnection(@"Data Source=seriesUpdates.db3"))
            {
                this.OpenConnexion();
                using (SQLiteCommand cmd = this._connexion.CreateCommand())
                {
                    cmd.CommandText = "CREATE TABLE series ('titre' VARCHAR(32) NULL, 'url' VARCHAR(128) NULL, 'saison' VARCHAR(32) NULL, 'episode' INT(11) NULL, 'active' BIT NULL DEFAULT 1);";
                    cmd.ExecuteReader();
                    cmd.Dispose();
                };

                this.CloseConnexion();
            };
        }

        public List<Serie> GetSeries()
        {
            List<Serie> listSeries = new List<Serie>();

            using (this._connexion = new SQLiteConnection(@"Data Source=seriesUpdates.db3"))
            {
                this.OpenConnexion();

                using (SQLiteCommand cmd = this._connexion.CreateCommand())
                {
                    cmd.CommandText = "SELECT * FROM series WHERE active = 1";
                    cmd.CommandType = CommandType.Text;
                    SQLiteDataReader read = cmd.ExecuteReader();

                    while(read.Read())
                    {
                        listSeries.Add(
                            new Serie(Convert.ToString(read["titre"]), 
                                Convert.ToString(read["url"]), 
                                Convert.ToString(read["saison"]), 
                                Convert.ToInt32(read["episode"])
                            )
                        );
                    }

                    cmd.Dispose();
                }

                this.CloseConnexion();
            };

            return listSeries;
        }

        public int GetNbSeries()
        {
           return this.GetSeries().Count;
        }

        public void UpdateSeries(string titre, string episode, string saison, Constants.serieUpdateType type = Constants.serieUpdateType.episode)
        {
            using (this._connexion = new SQLiteConnection(@"Data Source=seriesUpdates.db3"))
            {
                this.OpenConnexion();

                using (SQLiteCommand cmd = this._connexion.CreateCommand())
                {
                    switch (type)
                    {
                        case Constants.serieUpdateType.saison:
                            cmd.CommandText = "UPDATE series SET " + Constants.DB_COLUMN_NAME_SAISON + "=\"" + saison + "\", " + Constants.DB_COLUMN_NAME_EPISODE + "=\"-1\" WHERE titre=\"" + titre + "\"";
                            break;
                        default :
                            cmd.CommandText = "UPDATE series SET " + Constants.DB_COLUMN_NAME_EPISODE + "=\"" + Convert.ToInt32(episode) + "\" WHERE titre=\"" + titre + "\"";
                            break;
                    }
                    
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteReader();

                    cmd.Dispose();
                }

                this.CloseConnexion();
            };
        }
    }
}
